﻿Namespace B1Data
    ''' <summary>
    ''' 继承Datatable加入B1特点
    ''' </summary>
    ''' <remarks></remarks>
    Public Class B1DataTable
        Inherits System.Data.DataTable
        Implements B1Data.IB1DataTable

        Public SboCompany As SAPbobsCOM.Company
        Private _FieldNamesStr As String = ""
        Private _Owner As Object
        Private _TableType As SAPbobsCOM.BoUTBTableType
        Private _MaxDocEntry As Integer
        Private _SecondCodeRule As CodeRule
        Private _PrimaryKeyRule As CodeRule


        Public Sub New(ByVal TableName As String, ByVal B1Company As SAPbobsCOM.Company)
            MyBase.New(TableName)
            SboCompany = B1Company
        End Sub

        Public Sub New(ByVal TableName As String, ByVal B1Company As SAPbobsCOM.Company, ByVal Owner As Object, Optional ByVal CreateColumn As Boolean = False)
            MyBase.New(TableName)
            SboCompany = B1Company
            _Owner = Owner
            If CreateColumn Then
                Me.GetColumns()
            End If
            getTableType()
        End Sub

        ''' <summary>
        ''' 第二主键编码（LineId）
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Property SecondKeyRule As CodeRule
            Set(ByVal value As CodeRule)
                _SecondCodeRule = value
            End Set
            Get
                Return _SecondCodeRule
            End Get
        End Property

        ''' <summary>
        ''' 主键编码规则（DocEntry、Code）
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Property PrimaryKeyRule As CodeRule
            Set(ByVal value As CodeRule)
                _PrimaryKeyRule = value
            End Set
            Get
                Return _PrimaryKeyRule
            End Get
        End Property

        ''' <summary>
        ''' 字段名字符串，逗号分隔
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public ReadOnly Property ColumnsString As String
            Get
                Return Me.getColumnNames(",")
            End Get
        End Property

        ''' <summary>
        ''' 获取下个主键编码
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public ReadOnly Property NextPrimaryKeyValue As Object
            Get
                If _PrimaryKeyRule Is Nothing Then
                    Return Nothing
                Else
                    Return _PrimaryKeyRule.NextCode()
                End If
            End Get
        End Property

        Public ReadOnly Property NextSecondKeyValue As Object
            Get
                If _SecondCodeRule Is Nothing Then
                    Return Nothing
                Else
                    Return _SecondCodeRule.NextCode()
                End If
            End Get
        End Property

        ''' <summary>
        ''' 所属对象
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks>对象由多个表组成</remarks>
        Public ReadOnly Property Owner As Object
            Get
                Return _Owner
            End Get
        End Property

        ''' <summary>
        ''' 根据TableName自动创建列
        ''' </summary>
        ''' <remarks></remarks>
        Public Sub GetColumns()
            Dim oRs As SAPbobsCOM.Recordset = Nothing
            Dim rsField As SAPbobsCOM.Field = Nothing
            Dim sql_gettable As String = "select top 0 * from dbo.[%0]"
            Try
                If String.IsNullOrEmpty(TableName) Then Throw New Exception("no tablename.")
                oRs = SboCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
                oRs.DoQuery(sql_gettable.Replace("%0", Me.TableName))
                GetRecordsetColumns(oRs)
            Catch ex As Exception
                Throw
            Finally
                If Not rsField Is Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(rsField)
                If Not oRs Is Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(oRs)
            End Try
        End Sub

        ''' <summary>
        ''' 根据rs设置列
        ''' </summary>
        ''' <param name="oRs"></param>
        ''' <remarks></remarks>
        Private Sub GetRecordsetColumns(ByVal oRs As SAPbobsCOM.Recordset)
            Dim rsField As SAPbobsCOM.Field
            Dim dtCol As B1Data.B1DataColumn
            Dim rsVaildValues As SAPbobsCOM.ValidValues
            Dim rsVaildValue As SAPbobsCOM.ValidValue
            Try
                _FieldNamesStr = ""
                For i As Integer = 0 To oRs.Fields.Count - 1 Step 1
                    rsField = oRs.Fields.Item(i)
                    dtCol = New B1Data.B1DataColumn
                    dtCol.ColumnName = rsField.Name
                    dtCol.DataType = System.Type.GetType(GetFieldDotNetTypeSign(rsField))
                    Me.Columns.Add(dtCol)
                    rsVaildValues = rsField.ValidValues
                    For p As Integer = 0 To rsVaildValues.Count - 1
                        rsVaildValue = rsVaildValues.Item(p)
                        dtCol.ValidValues.Add(rsVaildValue.Value, rsVaildValue.Description)
                    Next
                    'Console.WriteLine(rsField.DefaultValue)
                    If Not String.IsNullOrEmpty(rsField.DefaultValue) Then
                        dtCol.DefaultValue = rsField.DefaultValue
                    End If
                Next
            Catch ex As Exception
                Throw
            End Try
        End Sub

        ''' <summary>
        ''' 转换b1数据类型到dotNet
        ''' </summary>
        ''' <param name="rsField"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Private Function GetFieldDotNetTypeSign(ByVal rsField As SAPbobsCOM.Field) As String
            Try
                Select Case rsField.Type
                    Case SAPbobsCOM.BoFieldTypes.db_Alpha
                        Select Case rsField.SubType
                            Case SAPbobsCOM.BoFldSubTypes.st_None
                                Return "System.String"
                            Case SAPbobsCOM.BoFldSubTypes.st_Address
                                Return "System.String"
                            Case SAPbobsCOM.BoFldSubTypes.st_Phone
                                Return "System.String"
                            Case SAPbobsCOM.BoFldSubTypes.st_Image
                                Return "System.String"
                            Case Else
                                Return "System.String"
                        End Select
                    Case SAPbobsCOM.BoFieldTypes.db_Date
                        Select Case rsField.SubType
                            Case SAPbobsCOM.BoFldSubTypes.st_None
                                Return "System.DateTime"
                            Case SAPbobsCOM.BoFldSubTypes.st_Time
                                Return "System.Int32"
                            Case Else
                                Return "System.String"
                        End Select
                    Case SAPbobsCOM.BoFieldTypes.db_Memo
                        Select Case rsField.SubType
                            Case SAPbobsCOM.BoFldSubTypes.st_None
                                Return "System.String"
                            Case SAPbobsCOM.BoFldSubTypes.st_Link
                                Return "System.String"
                            Case Else
                                Return "System.String"
                        End Select
                    Case SAPbobsCOM.BoFieldTypes.db_Float
                        Select Case rsField.SubType
                            Case SAPbobsCOM.BoFldSubTypes.st_Measurement
                                Return "System.Decimal"
                            Case SAPbobsCOM.BoFldSubTypes.st_Percentage
                                Return "System.Decimal"
                            Case SAPbobsCOM.BoFldSubTypes.st_Price
                                Return "System.Decimal"
                            Case SAPbobsCOM.BoFldSubTypes.st_Quantity
                                Return "System.Decimal"
                            Case SAPbobsCOM.BoFldSubTypes.st_Rate
                                Return "System.Decimal"
                            Case Else
                                Return "System.Decimal"
                        End Select
                    Case SAPbobsCOM.BoFieldTypes.db_Numeric
                        Select Case rsField.SubType
                            Case SAPbobsCOM.BoFldSubTypes.st_None
                                Return "System.Int32"
                            Case Else
                                Return "System.Int32"
                        End Select
                End Select
                Return "System.String"
            Catch ex As Exception
                Return "System.String"
            End Try
        End Function

        Public Function GetRowsToDbSQL() As String
            Dim sql_return As String = ""
            Try

                Return Nothing
            Catch ex As Exception
                Throw ex
            End Try
        End Function

        ''' <summary>
        ''' 设置值，如果未构建列，则自动构建。
        ''' </summary>
        ''' <param name="rs"></param>
        ''' <remarks></remarks>
        Public Sub SetValues(ByVal rs As SAPbobsCOM.Recordset)
            Dim dtRow As System.Data.DataRow
            Dim i As Integer
            Try
                If Me.Columns.Count = 0 Then
                    Me.GetRecordsetColumns(rs)
                End If
                Do Until rs.EoF
                    dtRow = Me.AddNewRow
                    For i = 0 To rs.Fields.Count - 1
                        dtRow(i) = rs.Fields.Item(i).Value
                    Next
                    Me.Rows.Add(dtRow)
                    rs.MoveNext()
                    If TableType = SAPbobsCOM.BoUTBTableType.bott_Document Then
                        If _PrimaryKeyRule Is Nothing Then
                        Else
                            _PrimaryKeyRule.RefreshCurrenCode(dtRow("DocEntry"))
                        End If
                    End If
                    dtRow.AcceptChanges()
                Loop
            Catch ex As Exception
                Throw ex
            End Try
        End Sub

        ''' <summary>
        ''' 根据语句填充值
        ''' </summary>
        ''' <param name="SQL"></param>
        ''' <remarks></remarks>
        Public Sub SetValues(ByVal SQL As String)
            Dim dtRow As System.Data.DataRow
            Dim Rs As SAPbobsCOM.Recordset
            Dim i As Integer
            Try
                Rs = SboCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
                Rs.DoQuery(SQL)
                If Me.Columns.Count = 0 Then
                    Me.GetRecordsetColumns(Rs)
                End If
                Do Until Rs.EoF
                    dtRow = Me.AddNewRow
                    For i = 0 To Rs.Fields.Count - 1
                        dtRow(i) = Rs.Fields.Item(i).Value
                    Next
                    Me.Rows.Add(dtRow)
                    If TableType = SAPbobsCOM.BoUTBTableType.bott_Document Then
                        If _PrimaryKeyRule Is Nothing Then
                        Else
                            _PrimaryKeyRule.RefreshCurrenCode(dtRow("DocEntry"))
                        End If
                    End If
                    Rs.MoveNext()
                    dtRow.AcceptChanges()
                Loop
                Me.Select()
            Catch ex As Exception
                Throw ex
            End Try
        End Sub

        ''' <summary>
        ''' 构造新行（不添加到DataTable）
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Overloads Function NewRow() As B1Data.B1DataRow
            Dim DataRow As Data.DataRow
            DataRow = MyBase.NewRow()
            Return (CType(DataRow, B1Data.B1DataRow))
        End Function

        ''' <summary>
        ''' 添加新行到datatable
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Overloads Function AddNewRow() As B1Data.B1DataRow
            Dim DataRow As B1Data.B1DataRow
            DataRow = Me.NewRow()
            setNewRowBaseValues(DataRow)
            Me.Rows.Add(DataRow)
            Return DataRow
        End Function

        ''' <summary>
        ''' 在datatable中查找行
        ''' </summary>
        ''' <param name="Filter">查找条件（Field = 'Niuren'）</param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Overloads Function [Select](ByVal Filter As String) As B1Data.B1DataRow()
            Return TransToB1TableRows(MyBase.Select(Filter))
        End Function

        ''' <summary>
        ''' 填充基本的数据
        ''' </summary>
        ''' <param name="DataRow"></param>
        ''' <remarks></remarks>
        Private Sub setNewRowBaseValues(ByVal DataRow As Data.DataRow)
            With DataRow
                If TableType = SAPbobsCOM.BoUTBTableType.bott_Document Then
                    .Item("DocEntry") = NextPrimaryKeyValue
                End If
                'If TableType = SAPbobsCOM.BoUTBTableType.bott_Document Or TableType = SAPbobsCOM.BoUTBTableType.bott_MasterData Then
                '    .Item("UserSign") = SboCompany.UserSignature
                '    .Item("CreateDate") = Me.getServerDate
                '    .Item("CreateTime") = Me.getServerTime
                'End If
            End With
        End Sub

        ''' <summary>
        ''' 获取数据库日期
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function getServerDate() As DateTime
            Dim oRs As SAPbobsCOM.Recordset
            oRs = SboCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            oRs.DoQuery("Select getdate()")
        End Function

        ''' <summary>
        ''' 获取数据库时间
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function getServerTime() As Int32
            Dim oRs As SAPbobsCOM.Recordset
            Dim time As DateTime
            oRs = SboCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            oRs.DoQuery("Select getdate()")
            time = oRs.Fields.Item(0).Value
            Return time.Hour * 100 + time.Minute
        End Function

        ''' <summary>
        ''' datarow转换为B1datarow
        ''' </summary>
        ''' <param name="Rows"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Private Function TransToB1TableRows(ByVal Rows As Data.DataRow()) As B1Data.B1DataRow()
            Dim B1Rows() As B1Data.B1DataRow
            If Rows Is Nothing Then
                Return Nothing
            Else
                If Rows.Length = 0 Then
                    ReDim B1Rows(0)
                Else
                    ReDim B1Rows(Rows.Length - 1)
                    Dim i As Integer = 0
                    For Each Row As Data.DataRow In Rows
                        B1Rows.SetValue(CType(Row, SAPBoAddon.B1Data.B1DataRow), i)
                        'B1Rows.SetValue(Row, i)
                        i += 1
                    Next
                End If
                Return B1Rows
            End If
        End Function

        ''' <summary>
        ''' 获取表类型
        ''' </summary>
        ''' <remarks></remarks>
        Private Sub getTableType() Implements IB1DataTable.getTableType
            Dim oRs As SAPbobsCOM.Recordset
            Dim sql_GetTableType As String = "SELECT *  FROM dbo.OUTB T0 WHERE TableName = '%0'"
            oRs = SboCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            oRs.DoQuery(sql_GetTableType.Replace("%0", Me.TableName.Replace("@", "")))
            If oRs.RecordCount > 0 Then
                _TableType = oRs.Fields.Item("ObjectType").Value
            End If
            setPrimaryKey()
        End Sub

        ''' <summary>
        ''' 配置表的默认主键
        ''' </summary>
        ''' <remarks></remarks>
        Public Sub setPrimaryKey()
            Dim col() As Data.DataColumn
            Dim B1Col As B1Data.B1DataColumn
            If Me.Columns.Count > 0 Then
                Select Case TableType
                    Case SAPbobsCOM.BoUTBTableType.bott_Document
                        ReDim col(0)
                        col.SetValue(Me.Columns.Item("DocEntry"), 0)
                        Me.PrimaryKey = col
                        B1Col = col(0)
                        B1Col.CodeRule = New B1Data.CodeRuleDocEntry(Me, "DocEntry")
                        Me._PrimaryKeyRule = B1Col.CodeRule

                    Case SAPbobsCOM.BoUTBTableType.bott_DocumentLines
                        ReDim col(1)
                        col.SetValue(Me.Columns.Item("DocEntry"), 0)
                        col.SetValue(Me.Columns.Item("LineId"), 1)
                        Me.PrimaryKey = col
                    Case SAPbobsCOM.BoUTBTableType.bott_MasterData
                        ReDim col(0)
                        col.SetValue(Me.Columns.Item("Code"), 0)
                        Me.PrimaryKey = col
                    Case SAPbobsCOM.BoUTBTableType.bott_MasterDataLines
                        ReDim col(1)
                        col.SetValue(Me.Columns.Item("Code"), 0)
                        col.SetValue(Me.Columns.Item("LineId"), 1)
                        Me.PrimaryKey = col
                    Case SAPbobsCOM.BoUTBTableType.bott_NoObject
                        ReDim col(0)
                        col.SetValue(Me.Columns.Item("Code"), 0)
                        Me.PrimaryKey = col
                End Select
            End If
        End Sub

        ''' <summary>
        ''' 添加主键
        ''' </summary>
        ''' <param name="ColumnName">字段名</param>
        ''' <remarks></remarks>
        Public Sub addPrimaryKey(ByVal ColumnName As String)
            Dim col() As Data.DataColumn
            If Me.Columns.Count > 0 Then
                If Me.Columns.Contains(ColumnName) Then
                    ReDim col(0)
                    col.SetValue(Me.Columns.Item(ColumnName), 0)
                    Me.PrimaryKey = col

                End If

            End If
        End Sub

        ''' <summary>
        ''' 窗体类型
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public ReadOnly Property TableType As SAPbobsCOM.BoUTBTableType Implements IB1DataTable.TableType
            Get
                Return _TableType
            End Get
        End Property

        ''' <summary>
        ''' 根据默认主键检索行
        ''' </summary>
        ''' <param name="Key"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function RowItem(ByVal ParamArray Key() As Object) As B1DataRow Implements IB1DataTable.RowItem
            Dim Key1 As String
            Dim Key2 As String
            Dim Rows() As B1Data.B1DataRow
            Select Case Me.TableType
                Case SAPbobsCOM.BoUTBTableType.bott_Document
                    If Key.Length = 1 Then
                        Key1 = Key(0)
                        Rows = Me.Select("DocEntry = %0 ".Remove("%0", Key1))
                        If Rows.Length = 0 Then
                            Return Nothing
                        Else
                            Return Rows(0)
                        End If
                    Else
                        Throw New Exception("参数个数必须为1个。")
                    End If
                Case SAPbobsCOM.BoUTBTableType.bott_DocumentLines
                    If Key.Length = 2 Then
                        Key1 = Key(0)
                        Key2 = Key(1)
                        Rows = Me.Select("DocEntry = %0 And LineId = %1 ".Remove("%0", Key1).Remove("%1", Key2))
                        If Rows.Length = 0 Then
                            Return Nothing
                        Else
                            Return Rows(0)
                        End If
                    Else
                        Throw New Exception("参数个数必须为2个。")
                    End If
                Case SAPbobsCOM.BoUTBTableType.bott_MasterData
                    If Key.Length = 1 Then
                        Key1 = Key(0)
                        Rows = Me.Select("Code = '%0' ".Remove("%0", Key1))
                        If Rows.Length = 0 Then
                            Return Nothing
                        Else
                            Return Rows(0)
                        End If
                    Else
                        Throw New Exception("参数个数必须为1个。")
                    End If
                Case SAPbobsCOM.BoUTBTableType.bott_MasterDataLines
                    If Key.Length = 2 Then
                        Key1 = Key(0)
                        Key2 = Key(1)
                        Rows = Me.Select("Code = %0 And LineId = %1 ".Remove("%0", Key1).Remove("%1", Key2))
                        If Rows.Length = 0 Then
                            Return Nothing
                        Else
                            Return Rows(0)
                        End If
                    Else
                        Throw New Exception("参数个数必须为2个。")
                    End If
                Case SAPbobsCOM.BoUTBTableType.bott_NoObject
                    If Key.Length = 1 Then
                        Key1 = Key(0)
                        Key2 = Key(1)
                        Rows = Me.Select("Code = %0 ".Remove("%0", Key1))
                        If Rows.Length = 0 Then
                            Return Nothing
                        Else
                            Return Rows(0)
                        End If
                    Else
                        Throw New Exception("参数个数必须为1个。")
                    End If
            End Select
            Return Nothing
        End Function

        ''' <summary>
        ''' 获取列名称
        ''' </summary>
        ''' <param name="splitstring">分隔字符</param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function getColumnNames(Optional ByVal splitstring As String = vbTab) As String
            Dim tmpString As String = ""
            Dim Col As Data.DataColumn
            For Each Col In Me.Columns
                tmpString += Col.ColumnName & splitstring
            Next
            tmpString = tmpString.Remove(tmpString.Length - splitstring.Length, splitstring.Length)
            Return tmpString
        End Function

        ''' <summary>
        ''' 获取行值
        ''' （SQL格式后）
        ''' </summary>
        ''' <param name="splitstring"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function getRowValues(Optional ByVal splitstring As String = vbTab) As String
            Dim Values As String = ""
            Dim rowstring As String
            Dim row As DataRow
            Dim colCount As Integer
            For Each row In Me.Rows
                rowstring = ""
                For colCount = 0 To Me.Columns.Count - 1
                    rowstring += row.Item(colCount) & splitstring
                Next
                rowstring = rowstring.Remove(rowstring.Length - splitstring.Length, splitstring.Length)
                Values += rowstring & vbNewLine
            Next
            Return Values
        End Function

    End Class
End Namespace

